<!DOCTYPE html>
<html>
<head>
<title>anw.md</title>
<meta http-equiv="Content-type" content="text/html;charset=UTF-8">

<style>
/* https://github.com/microsoft/vscode/blob/master/extensions/markdown-language-features/media/markdown.css */
/*---------------------------------------------------------------------------------------------
 *  Copyright (c) Microsoft Corporation. All rights reserved.
 *  Licensed under the MIT License. See License.txt in the project root for license information.
 *--------------------------------------------------------------------------------------------*/

body {
	font-family: var(--vscode-markdown-font-family, -apple-system, BlinkMacSystemFont, "Segoe WPC", "Segoe UI", "Ubuntu", "Droid Sans", sans-serif);
	font-size: var(--vscode-markdown-font-size, 14px);
	padding: 0 26px;
	line-height: var(--vscode-markdown-line-height, 22px);
	word-wrap: break-word;
	background-color: #222;
	color: #aaa
}

#code-csp-warning {
	position: fixed;
	top: 0;
	right: 0;
	color: white;
	margin: 16px;
	text-align: center;
	font-size: 12px;
	font-family: sans-serif;
	background-color:#444444;
	cursor: pointer;
	padding: 6px;
	box-shadow: 1px 1px 1px rgba(0,0,0,.25);
}

#code-csp-warning:hover {
	text-decoration: none;
	background-color:#007acc;
	box-shadow: 2px 2px 2px rgba(0,0,0,.25);
}

body.scrollBeyondLastLine {
	margin-bottom: calc(100vh - 22px);
}

body.showEditorSelection .code-line {
	position: relative;
}

body.showEditorSelection .code-active-line:before,
body.showEditorSelection .code-line:hover:before {
	content: "";
	display: block;
	position: absolute;
	top: 0;
	left: -12px;
	height: 100%;
}

body.showEditorSelection li.code-active-line:before,
body.showEditorSelection li.code-line:hover:before {
	left: -30px;
}

.vscode-light.showEditorSelection .code-active-line:before {
	border-left: 3px solid rgba(0, 0, 0, 0.15);
}

.vscode-light.showEditorSelection .code-line:hover:before {
	border-left: 3px solid rgba(0, 0, 0, 0.40);
}

.vscode-light.showEditorSelection .code-line .code-line:hover:before {
	border-left: none;
}

.vscode-dark.showEditorSelection .code-active-line:before {
	border-left: 3px solid rgba(255, 255, 255, 0.4);
}

.vscode-dark.showEditorSelection .code-line:hover:before {
	border-left: 3px solid rgba(255, 255, 255, 0.60);
}

.vscode-dark.showEditorSelection .code-line .code-line:hover:before {
	border-left: none;
}

.vscode-high-contrast.showEditorSelection .code-active-line:before {
	border-left: 3px solid rgba(255, 160, 0, 0.7);
}

.vscode-high-contrast.showEditorSelection .code-line:hover:before {
	border-left: 3px solid rgba(255, 160, 0, 1);
}

.vscode-high-contrast.showEditorSelection .code-line .code-line:hover:before {
	border-left: none;
}

img {
	max-width: 100%;
	max-height: 100%;
}

a {
	text-decoration: none;
}

a:hover {
	text-decoration: underline;
}

a:focus,
input:focus,
select:focus,
textarea:focus {
	outline: 1px solid -webkit-focus-ring-color;
	outline-offset: -1px;
}

hr {
	border: 0;
	height: 2px;
	border-bottom: 2px solid;
}

h1 {
	padding-bottom: 0.3em;
	line-height: 1.2;
	border-bottom-width: 1px;
	border-bottom-style: solid;
}

h1, h2, h3 {
	font-weight: normal;
}

table {
	border-collapse: collapse;
}

table > thead > tr > th {
	text-align: left;
	border-bottom: 1px solid;
}

table > thead > tr > th,
table > thead > tr > td,
table > tbody > tr > th,
table > tbody > tr > td {
	padding: 5px 10px;
}

table > tbody > tr + tr > td {
	border-top: 1px solid;
}

blockquote {
	margin: 0 7px 0 5px;
	padding: 0 16px 0 10px;
	border-left-width: 5px;
	border-left-style: solid;
}

code {
	font-family: Menlo, Monaco, Consolas, "Droid Sans Mono", "Courier New", monospace, "Droid Sans Fallback";
	font-size: 1em;
	line-height: 1.357em;
}

body.wordWrap pre {
	white-space: pre-wrap;
}

pre:not(.hljs),
pre.hljs code > div {
	padding: 16px;
	border-radius: 3px;
	overflow: auto;
}

pre code {
	color: var(--vscode-editor-foreground);
	tab-size: 4;
}

/** Theming */

.vscode-light pre {
	background-color: rgba(220, 220, 220, 0.4);
}

.vscode-dark pre {
	background-color: rgba(10, 10, 10, 0.4);
}

.vscode-high-contrast pre {
	background-color: rgb(0, 0, 0);
}

.vscode-high-contrast h1 {
	border-color: rgb(0, 0, 0);
}

.vscode-light table > thead > tr > th {
	border-color: rgba(0, 0, 0, 0.69);
}

.vscode-dark table > thead > tr > th {
	border-color: rgba(255, 255, 255, 0.69);
}

.vscode-light h1,
.vscode-light hr,
.vscode-light table > tbody > tr + tr > td {
	border-color: rgba(0, 0, 0, 0.18);
}

.vscode-dark h1,
.vscode-dark hr,
.vscode-dark table > tbody > tr + tr > td {
	border-color: rgba(255, 255, 255, 0.18);
}

</style>

<style>
/* a11y-dark theme */
/* Based on the Tomorrow Night Eighties theme: https://github.com/isagalaev/highlight.js/blob/master/src/styles/tomorrow-night-eighties.css */
/* @author: ericwbailey */

/* Comment */
.hljs-comment,
.hljs-quote {
  color: #d4d0ab;
}

/* Red */
.hljs-variable,
.hljs-template-variable,
.hljs-tag,
.hljs-name,
.hljs-selector-id,
.hljs-selector-class,
.hljs-regexp,
.hljs-deletion {
  color: #ffa07a;
}

/* Orange */
.hljs-number,
.hljs-built_in,
.hljs-builtin-name,
.hljs-literal,
.hljs-type,
.hljs-params,
.hljs-meta,
.hljs-link {
  color: #f5ab35;
}

/* Yellow */
.hljs-attribute {
  color: #ffd700;
}

/* Green */
.hljs-string,
.hljs-symbol,
.hljs-bullet,
.hljs-addition {
  color: #abe338;
}

/* Blue */
.hljs-title,
.hljs-section {
  color: #00e0e0;
}

/* Purple */
.hljs-keyword,
.hljs-selector-tag {
  color: #dcc6e0;
}

.hljs {
  display: block;
  overflow-x: auto;
  background: #2b2b2b;
  color: #f8f8f2;
  padding: 0.5em;
}

.hljs-emphasis {
  font-style: italic;
}

.hljs-strong {
  font-weight: bold;
}

@media screen and (-ms-high-contrast: active) {
  .hljs-addition,
  .hljs-attribute,
  .hljs-built_in,
  .hljs-builtin-name,
  .hljs-bullet,
  .hljs-comment,
  .hljs-link,
  .hljs-literal,
  .hljs-meta,
  .hljs-number,
  .hljs-params,
  .hljs-string,
  .hljs-symbol,
  .hljs-type,
  .hljs-quote {
        color: highlight;
    }

    .hljs-keyword,
    .hljs-selector-tag {
        font-weight: bold;
    }
}

</style>

<style>
/*
 * Markdown PDF CSS
 */

 body {
	font-family: -apple-system, BlinkMacSystemFont, "Segoe WPC", "Segoe UI", "Ubuntu", "Droid Sans", sans-serif, "Meiryo";
	padding: 0 12px;
}

pre {
	background-color: #f8f8f8;
	border: 1px solid #cccccc;
	border-radius: 3px;
	overflow-x: auto;
	white-space: pre-wrap;
	overflow-wrap: break-word;
}

pre:not(.hljs) {
	padding: 23px;
	line-height: 19px;
}

blockquote {
	background: rgba(127, 127, 127, 0.1);
	border-color: rgba(0, 122, 204, 0.5);
}

.emoji {
	height: 1.4em;
}

code {
	font-size: 14px;
	line-height: 19px;
}

/* for inline code */
:not(pre):not(.hljs) > code {
	color: #C9AE75; /* Change the old color so it seems less like an error */
	font-size: inherit;
}

/* Page Break : use <div class="page"/> to insert page break
-------------------------------------------------------- */
.page {
	page-break-after: always;
}

</style>

<script src="https://unpkg.com/mermaid/dist/mermaid.min.js"></script>
</head>
<body>
  <script>
    mermaid.initialize({
      startOnLoad: true,
      theme: document.body.classList.contains('vscode-dark') || document.body.classList.contains('vscode-high-contrast')
          ? 'dark'
          : 'default'
    });
  </script>
<blockquote>
<p>whuserXXX 172.31.0.116 客户端密码 training<br>
root密码是 Asimov 爱死行动
git clone https://gitee.com/tearf001/exe .
mv <em>.repo
yum clean all &amp;&amp; yum repolist
或者
scp -r root@172.31.0.115:/etc/yum.repos.d/</em>.repo /etc/yum.repos.d/</p>
</blockquote>
<h1 id="0-%E8%BF%9B%E5%85%A5">0. 进入</h1>
<p>su root</p>
<h1 id="1-%E5%88%9B%E5%BB%BA%E7%94%A8%E6%88%B7%E5%92%8C%E7%94%A8%E6%88%B7%E7%BB%84">1 创建用户和用户组</h1>
<pre class="hljs"><code><div>groupadd whgroup116 <span class="hljs-comment"># 用户组</span>
useradd whuser116 -d /data/whuser116 -g whgroup116 <span class="hljs-comment"># 用户 home和组</span>
</div></code></pre>
<h1 id="2%E6%8C%89%E4%BB%A5%E4%B8%8B%E8%A6%81%E6%B1%82%E5%AE%8C%E6%88%90-%E6%96%87%E4%BB%B6%E7%9B%AE%E5%BD%95%E5%8F%8A%E6%89%93%E5%8C%85%E6%93%8D%E4%BD%9C">2、按以下要求完成 文件、目录及打包操作</h1>
<pre class="hljs"><code><div>mkdir -p /data/exam/ <span class="hljs-comment"># 创建文件夹</span>
<span class="hljs-built_in">echo</span> $(hostname -I) &gt; ahcnuser116 <span class="hljs-comment"># 得到IP地址写入文件 或者通过 ip addr show ens192 得到IP</span>
tar -czvf ssh.tar.gz /etc/ssh/ <span class="hljs-comment"># 格式:  tar czf output /path/to/ssh/</span>
</div></code></pre>
<h1 id="3%E6%8C%89%E4%BB%A5%E4%B8%8B%E8%A6%81%E6%B1%82%E8%AE%BE%E7%BD%AE%E6%96%87%E4%BB%B6%E6%9D%83%E9%99%90">3、按以下要求设置文件权限</h1>
<pre class="hljs"><code><div>mkdir -p /data/adminer <span class="hljs-comment"># 创建文件夹：/data/adminer</span>
chown whuser116:whgroup116 /data/adminer <span class="hljs-comment">#  目录的拥有人拥有目录的所有权限: 格式: chown u:g dir</span>
chmod 760 /data/adminer <span class="hljs-comment"># 目录权限分配 用户111=7, 组110=6 其他组000=0</span>
setfacl -m u:training:r-- /data/adminer <span class="hljs-comment"># 用户 training 用户对目录只有读权限 setfacl -m u:&lt;user&gt;:xxx /data/adminer</span>
chmod g+s /data/adminer <span class="hljs-comment"># 用户组嵌套 g + s(ubdirectory)</span>
</div></code></pre>
<h1 id="4%E6%8C%89%E4%BB%A5%E4%B8%8B%E8%A6%81%E6%B1%82%E4%B8%BA%E7%94%A8%E6%88%B7-whuserx-%E5%88%9B%E5%BB%BA%E8%AE%A1%E5%88%92%E4%BB%BB%E5%8A%A1">4、按以下要求为用户 whuserX 创建计划任务</h1>
<pre class="hljs"><code><div><span class="hljs-comment"># 可选 编辑/etc/sudoers，提升whuser116权限有sudo权限 </span>
crontab -e -u whuser116
<span class="hljs-comment"># 写入 20 15 * * * sudo systemctl restart rsyslog.service</span>
</div></code></pre>
<blockquote>
<p>验证 cat /var/spool/cron/whuser116</p>
</blockquote>
<h1 id="5%E6%8C%89%E8%A6%81%E6%B1%82%E9%85%8D%E7%BD%AEweb%E6%9C%8D%E5%8A%A1%E5%99%A8">5、按要求配置WEB服务器</h1>
<pre class="hljs"><code><div>yum install httpd
<span class="hljs-built_in">echo</span> <span class="hljs-string">'listen 8888'</span> &gt;&gt; /etc/httpd/conf/httpd.conf
<span class="hljs-built_in">echo</span> <span class="hljs-string">"hello whuser116"</span> &gt;&gt; /var/www/html/index.html
systemctl <span class="hljs-built_in">enable</span> --now httpd
<span class="hljs-comment"># yum provides '*/modules/mod_ldap.so'</span>
systemctl start httpd
systemctl status httpd
<span class="hljs-comment"># 验证：</span>
curl http://172.31.0.116:8888
</div></code></pre>
<p>Python部分</p>
<pre class="hljs"><code><div>yum install python3 -y
</div></code></pre>
<p>1、</p>
<pre class="hljs"><code><div><span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">main</span><span class="hljs-params">(s)</span>:</span>
    print(s+<span class="hljs-string">'&gt;&gt;'</span>+str(len(s)))

<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    main(<span class="hljs-string">'helloworld'</span>)
<span class="hljs-comment"># 2</span>
<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">scoreFun</span><span class="hljs-params">(s)</span>:</span>
    <span class="hljs-keyword">if</span> <span class="hljs-number">100</span> &gt;= s &gt;= <span class="hljs-number">90</span>:
        <span class="hljs-keyword">return</span> <span class="hljs-string">'A'</span>
    <span class="hljs-keyword">elif</span> s &gt;= <span class="hljs-number">60</span>:
        <span class="hljs-keyword">return</span> <span class="hljs-string">'B'</span>
    <span class="hljs-keyword">elif</span> s &gt;= <span class="hljs-number">0</span>:
        <span class="hljs-keyword">return</span> <span class="hljs-string">'C'</span>
    <span class="hljs-keyword">else</span>:
        print(<span class="hljs-string">'无效成绩'</span>)

<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">'__main__'</span>:
    s = <span class="hljs-number">77</span>    
    print(<span class="hljs-string">'{}&gt;&gt;{}'</span>.format(s,scoreFun(s)))
</div></code></pre>
<h1 id="mysql%E9%83%A8%E5%88%86">MySQL部分：</h1>
<blockquote>
<p>安装(可选)</p>
</blockquote>
<pre class="hljs"><code><div>yum install mysql -y
sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
yum install mysql-server -y
service mysqld start
mysql -u root -p r00tpa55 <span class="hljs-comment"># 黏贴密码 默认密码</span>
</div></code></pre>
<p>1、root密码设置</p>
<pre class="hljs"><code><div>su - root <span class="hljs-comment"># 如果必要</span>
mysql
</div></code></pre>
<h1 id="mysql-ddldml">mysql ddl/dml</h1>
<pre class="hljs"><code><div><span class="hljs-keyword">use</span> mysql;
MariaDB [mysql]&gt; <span class="hljs-keyword">update</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">set</span> <span class="hljs-keyword">Password</span> = <span class="hljs-keyword">password</span>(<span class="hljs-string">'r00tpa55'</span>) <span class="hljs-keyword">where</span> <span class="hljs-keyword">User</span> = <span class="hljs-string">'root'</span>;
MariaDB [mysql]&gt; <span class="hljs-keyword">select</span> Host,<span class="hljs-keyword">User</span>,<span class="hljs-keyword">Password</span> <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span>;
MariaDB [mysql]&gt; <span class="hljs-keyword">flush</span> <span class="hljs-keyword">privileges</span>; <span class="hljs-comment">-- 必须 否则需要重启数据库服务</span>
</div></code></pre>
<p>验证：<code>mysql -u root -pr00tpa55</code></p>
<p>2、导库
该文件缺两条语句,增加在前面：
<code>wget http://172.31.1.60/download/myexamDB.sql</code></p>
<h2 id="%E5%88%9B%E5%BB%BA%E6%95%B0%E6%8D%AE%E5%BA%93">创建数据库</h2>
<pre class="hljs"><code><div>su - root
mysql `create database examDB;`
mysql -u root -pr00tpa55 -D examDB  &lt; myexamDB.sql;
</div></code></pre>
<p>3，查询
安装：</p>
<pre class="hljs"><code><div>yum install python
<span class="hljs-built_in">export</span> pxy=https://pypi.douban.com/simple

python -m pip install -i <span class="hljs-variable">$pxy</span> --upgrade pip
<span class="hljs-comment"># 如果上一步失败 &gt;&gt;&gt; wget https://bootstrap.pypa.io/get-pip.py </span>
python get-pip.py
</div></code></pre>
<p>安装模块</p>
<pre class="hljs"><code><div>pip3 install -i <span class="hljs-variable">$pxy</span> mysql-connector
</div></code></pre>
<pre class="hljs"><code><div><span class="hljs-keyword">import</span> mysql.connector <span class="hljs-keyword">as</span> myconn
<span class="hljs-comment"># conn = myconn.connect(host='127.0.0.1',user='root',passwd='r00tpa55',database='examDB')</span>
conn = myconn.connect(user=<span class="hljs-string">'root'</span>,passwd=<span class="hljs-string">'r00tpa55'</span>,database=<span class="hljs-string">'examDB'</span>)
cur = conn.cursor(buffered=<span class="hljs-literal">True</span>)
qs = {
    <span class="hljs-number">1</span>:<span class="hljs-string">"select count(*) from student where year(now()) - year(birth) between 31 and 33"</span>,
    <span class="hljs-number">2</span>:<span class="hljs-string">"select count(*) from student where department = '计算机系'"</span>,
    <span class="hljs-number">3</span>:<span class="hljs-string">"select max(grade) from score where c_name = '英语'"</span>,
    <span class="hljs-number">4</span>:<span class="hljs-string">"select c.grade from score c,student s where c.c_name = '中文' and c.stu_id = s.id and s.name = '李逵'"</span>,
    <span class="hljs-number">5</span>:<span class="hljs-string">"select sum(c.grade) from score c,student s where c.stu_id = s.id and s.name = '武松'"</span>,
}
r = []
<span class="hljs-keyword">for</span> seq, q <span class="hljs-keyword">in</span> qs.items():
    cur.execute(q)
    ans = cur.fetchone()[<span class="hljs-number">0</span>]
    r.append((seq, q, ans))

conn.close()
print(r)

<span class="hljs-keyword">with</span> open(<span class="hljs-string">'/data/my-ans.txt'</span>,<span class="hljs-string">'wt'</span>) <span class="hljs-keyword">as</span> f:
    <span class="hljs-keyword">for</span> i,q,a <span class="hljs-keyword">in</span> r:
        f.write(<span class="hljs-string">'{} {}\n'</span>.format(i, a))
    f.close()
</div></code></pre>
<p>4、</p>
<pre class="hljs"><code><div>(6) <span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> student2 <span class="hljs-keyword">drop</span> s_age;
(7) <span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> student2 <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">9</span>;
(8) <span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> student2 <span class="hljs-keyword">add</span> s_sex <span class="hljs-built_in">char</span>(<span class="hljs-number">1</span>);
(9) <span class="hljs-keyword">update</span> student2 <span class="hljs-keyword">set</span> s_sex = <span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> <span class="hljs-keyword">id</span> <span class="hljs-keyword">in</span> (<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>,<span class="hljs-number">4</span>) <span class="hljs-keyword">then</span> <span class="hljs-string">'男'</span> <span class="hljs-keyword">else</span> <span class="hljs-string">'女'</span> <span class="hljs-keyword">end</span>;
(10)<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> student2 (s_name,s_sex) <span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>, sex <span class="hljs-keyword">from</span> student <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">910</span>; 
</div></code></pre>
<h1 id="postgresql%E9%83%A8%E5%88%86">postgresql部分</h1>
<pre class="hljs"><code><div>sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
<span class="hljs-comment"># 13 版本</span>
sudo yum install -y postgresql13-server
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl <span class="hljs-built_in">enable</span> postgresql-13
sudo systemctl start postgresql-13

<span class="hljs-comment"># 或者 根据平台安装默认版本 RHEL8 13,12,10,9.6, R7: 9.2. ..</span>
sudo yum install -y postgresql-server
sudo /usr/bin/postgresql-setup initdb
sudo systemctl <span class="hljs-built_in">enable</span> postgresql
<span class="hljs-comment"># sudo systemctl stop postgresql-13</span>
sudo systemctl start postgresql
</div></code></pre>
<h2 id="%E8%AE%BE%E7%BD%AE%E5%AF%86%E7%A0%81%E5%88%9B%E5%BB%BA-examdb">设置密码,创建 examdb</h2>
<pre class="hljs"><code><div>su - postgres
psql
ALTER USER postgres WITH PASSWORD <span class="hljs-string">'abcd'</span>; <span class="hljs-comment"># 老的版本可能是 \password</span>
create database examdb;
</div></code></pre>
<p>psql -U postgres -h 127.0.0.1 -d examdb</p>
<h2 id="1%E5%AF%BC%E5%85%A5%E5%BA%93">1、导入库</h2>
<pre class="hljs"><code><div>wget http://172.31.1.60/download/pgexamDB.sql
su root
psql -U postgres -h 127.0.0.1 -f /data/pgexamDB.sql examdb <span class="hljs-comment"># host必须指定,密码不能再命令行中出现</span>
</div></code></pre>
<p>2、查询</p>
<pre class="hljs"><code><div>sudo yum install -y python3-devel postgresql-devel gcc
<span class="hljs-built_in">export</span> pxy=https://pypi.douban.com/simple
pip3 install -i <span class="hljs-variable">$pxy</span> psycopg2
</div></code></pre>
<pre class="hljs"><code><div><span class="hljs-keyword">import</span> psycopg2 <span class="hljs-keyword">as</span> pg
<span class="hljs-keyword">with</span> pg.connect(host=<span class="hljs-string">'127.0.0.1'</span>, database=<span class="hljs-string">"examdb"</span>,user=<span class="hljs-string">"postgres"</span>,password=<span class="hljs-string">"abcd"</span>,port=<span class="hljs-string">"5432"</span>) <span class="hljs-keyword">as</span> conn: <span class="hljs-comment"># host 必须要,否则该peer to trust</span>
    cur=conn.cursor()
    <span class="hljs-comment"># 语文成绩最高的</span>
    qs = {
        <span class="hljs-number">1</span>: <span class="hljs-string">"select string_agg(st.s_name,' '), score from score s join course c on s.c_id = c.id and c.c_name='语文' join student st on st.id = s.s_id group by score order by 2 desc"</span>,
        <span class="hljs-number">2</span>: <span class="hljs-string">"select count(1) from student st,course c,score s where st.id=s.s_id and s.c_id=c.id and c.c_name='英语' and score&gt;(select avg(score) from score ss where ss.c_id = c.id)"</span>,
        <span class="hljs-number">3</span>: <span class="hljs-string">"select count(1) from student st,course c,score s where st.id=s.s_id and s.c_id=c.id and c.c_name ='数学'"</span>,
        <span class="hljs-number">4</span>: <span class="hljs-string">"select avg(c.score)::numeric(4,2) from student s,score c where c.s_id = s.id and s.s_name = '赵云'"</span>,
        <span class="hljs-number">5</span>: <span class="hljs-string">"select avg(c.score)::numeric(4,2) from score c,course r,student s where r.c_name = '语文' and c.c_id = r.id and c.s_id = s.id and s.s_sex = '女'"</span>
    }
    r = []
    <span class="hljs-keyword">for</span> i, q <span class="hljs-keyword">in</span> qs.items():
        cur.execute(q)
        r.append((i, q, cur.fetchone()[<span class="hljs-number">0</span>]))

<span class="hljs-keyword">with</span> open(<span class="hljs-string">'/data/pg-ans.txt'</span>,<span class="hljs-string">'wt'</span>) <span class="hljs-keyword">as</span> f:
    <span class="hljs-keyword">for</span> i, q, a <span class="hljs-keyword">in</span> r:
        f.write(<span class="hljs-string">'{} {}\n'</span>.format(i, a))
</div></code></pre>
<p>3、建表</p>
<pre class="hljs"><code><div><span class="hljs-keyword">create</span> <span class="hljs-keyword">sequence</span> myanser_id_seq;
<span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> myanser (
    <span class="hljs-keyword">id</span> <span class="hljs-built_in">int</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span> <span class="hljs-keyword">default</span> <span class="hljs-keyword">nextval</span>(<span class="hljs-string">'myanser_id_seq'</span>) primary <span class="hljs-keyword">key</span>,
    <span class="hljs-keyword">name</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">20</span>),
    host <span class="hljs-built_in">varchar</span>(<span class="hljs-number">40</span>)
);

<span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> myanser (
    <span class="hljs-keyword">id</span> <span class="hljs-built_in">serial</span> primary <span class="hljs-keyword">key</span>,
    <span class="hljs-keyword">name</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">20</span>),
    host <span class="hljs-built_in">varchar</span>(<span class="hljs-number">40</span>)
);
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> myanser(<span class="hljs-keyword">name</span>,host) <span class="hljs-keyword">values</span>(<span class="hljs-string">'王新海'</span>,<span class="hljs-string">'whuser116'</span>);</div></code></pre>

</body>
</html>
